CREATE TRIGGER trig_professor ON section
AFTER UPDATE
AS
BEGIN
DECLARE @faculty_name VARCHAR(50)
DECLARE @section_id INTEGER
DECLARE @start_time INTEGER  
DECLARE @end_time INTEGER  
DECLARE @meeting_id INTEGER
DECLARE @weekday VARCHAR(20)
DECLARE @quarter VARCHAR(20)
DECLARE @year INTEGER
DECLARE @class_id INTEGER

SELECT @faculty_name = (SELECT faculty_name FROM inserted)
SELECT @section_id = (SELECT section_id FROM inserted)
SELECT @class_id = (SELECT class_id FROM section WHERE section_id = @section_id)
SELECT @quarter = (SELECT quarter FROM class WHERE class.class_id = @class_id)
SELECT @year = (SELECT year FROM class WHERE class.class_id = @class_id)

SELECT @meeting_id = (SELECT meeting_id FROM meeting WHERE section_id = @section_id
												AND type = 'LE')
SELECT @start_time = (SELECT start_time FROM meeting WHERE section_id = @section_id
												AND type = 'LE')
SELECT @end_time = (SELECT end_time FROM meeting WHERE section_id = @section_id
												AND type = 'LE')
SELECT @weekday = (SELECT weekday FROM meeting WHERE section_id = @section_id
												AND type = 'LE')

IF EXISTS (SELECT * FROM class,section,meeting WHERE faculty_name = @faculty_name 
						AND class.class_id = section.class_id
						AND class.quarter = @quarter
						AND class.year = @year
						AND section.section_id = meeting.section_id
						AND meeting.weekday = @weekday
						AND @end_time > start_time AND @start_time < end_time 
						AND NOT @meeting_id = meeting_id
						AND type = 'LE')
	BEGIN
		ROLLBACK TRANSACTION
		RAISERROR ('Sorry, Conflict section for the professor.',12, 1)
	END
END
